﻿using System;
using System.Linq;
using System.Linq.Expressions;
using SBACN.WebSite.Code;
using SBACN.WebSite.Common;
using SBACN.WebSite.Models;
using System.IO;
using System.Data.OleDb;

namespace SBACN.WebSite.Education
{
	public partial class ApplyList : MemberBasePage
	{
		protected int MID
		{
			get
			{
				return Convert.ToInt32(UserInfo.MemberID);
			}
		}

		protected void Page_Load(object sender, EventArgs e)
		{
			if (!Page.IsPostBack)
			{
				ShowMemberName();
				ShowList(1);
			}
		}
		protected void ShowMemberName()
		{
			if (this.MID != -1)
			{
				Expression<Func<TB_Member, bool>> f = (p => p.ID == this.MID);
				var result = DAL.DALHelper.Instance.GetFirstOrDefaultObject<TB_Member>(f);
				if (result != null)
				{
					this.MemberName.Text = string.IsNullOrWhiteSpace(result.Name) ? "&lt;N/A&gt;" : result.Name;
				}
				else
				{
					this.MemberName.Text = "&lt;N/A&gt;";
				}
			}
			else
			{
				this.MemberName.Text = "&lt;N/A&gt;";
			}
		}

		protected void ShowList(int pageIndex)
		{
			int records = 0;
			Expression<Func<Exam_Memeber_Quota, bool>> f;
			if (this.MID != -1)
			{
				f = (p => p.MemberID == this.MID && p.Status == 0);
			}
			else
			{
				f = (p => p.Status == 0);
			}

			Expression<Func<Exam_Memeber_Quota, DateTime>> order = (c => c.Deadline);
			var resultQ = DAL.DALHelper.Instance.GetList<Exam_Memeber_Quota, DateTime>(f, order, false, this.Pager1.PageSize, pageIndex, out records);

			this.ExamList.DataSource = resultQ;
			this.ExamList.DataBind();
			this.Pager1.RecordCount = records;
			this.Pager1.InitControl();
		}

		public string DisplayD(string date)
		{
			DateTime Now = DateTime.Now.AddDays(-1);
			DateTime Old = Convert.ToDateTime(date);
			if (Now > Old)
			{
				return "";
			}
			else
			{
				return "<a href='../Template/考试报名申请表.xls'>下载</a>";
			}
		}

		public string DisplayU(string date, string id)
		{
			DateTime Now = DateTime.Now.AddDays(-1);
			DateTime Old = Convert.ToDateTime(date);
			if (Now >= Old || this.MID == -1)
			{
				return "";
			}
			else
			{
				return "<a href='javascript:void(0)' onclick='upfile(" + id + ")'>上传</a>";
			}
		}

		public void BindSelectedPageIndexChanged(int pageIndex, int nextPage)
		{
			ShowList(nextPage);
		}

		protected void Unnamed1_Click(object sender, EventArgs e)
		{
			int id = this.MID;
			int validColNumber = 13;

			int ExamID;
			if (int.TryParse(this.HidSubjectID.Value, out ExamID) && id != 0)
			{
				string extension = GetFileExt(this.FileUpload.FileName);
				if ((",xlsx,xls,").ToLower().IndexOf(("," + extension + ",").ToLower()) < 1)
				{
					ClientScript.RegisterClientScriptBlock(this.GetType(), "AlertMsg", "<script type='text/javascript'>alert('上传的文件格式不正确,请重新上传！');</script>");
					return;
				}
				if (this.FileUpload.FileBytes.Length == 0 || this.FileUpload.FileBytes.Length > (MaxUploadSize * 1024 * 1024))
				{
					ClientScript.RegisterClientScriptBlock(this.GetType(), "AlertMsg", "<script type='text/javascript'>alert('上传的文件大小不符合系统设置,请重新上传！');</script>");
					return;
				}

				string Url = BasePage.UploadFile(SystemCode, this.FileUpload.FileName, this.FileUpload.FileBytes);
				TB_UploadInfo upload = DAL.DALHelper.Instance.Create<TB_UploadInfo>();
				upload.CreateBy = id.ToString();
				upload.CreateDate = DateTime.Now;
				upload.FilePath = Url;
				upload.FileType = BasePage.GetFileExt(this.FileUpload.FileName);
				upload.Name = "考试报名上传";
				upload.PrimaryID = ExamID;
				upload.Type = SystemCode;
				DAL.DALHelper.Instance.Add<TB_UploadInfo>(upload);
				DAL.DALHelper.Instance.SaveChanges();

				string sheel = "Sheet1";
				Expression<Func<TB_ExamQuota, bool>> f = (p => p.MemberID == id && p.ExamID == ExamID);
				var resultN = DAL.DALHelper.Instance.GetFirstOrDefaultObject<TB_ExamQuota>(f);
				var DI = ExcelAccess.GetExcelRows(Server.MapPath(Url), sheel);

				if (DI.Tables[0].Columns.Count < validColNumber)
				{
					ClientScript.RegisterClientScriptBlock(this.GetType(), "AlertMsg", "<script type='text/javascript'>alert('上传的文件格式不正确,请重新上传！');</script>");
					return;
				}
				if (DI.Tables[0].Rows.Count > resultN.Count)
				{
					ClientScript.RegisterClientScriptBlock(this.GetType(), "AlertMsg", "<script type='text/javascript'>alert('报名人数超过规定限额,文件上传失败！');</script>");
					return;
				}

				// 删除以前上传数据
				Expression<Func<TB_Score, bool>> f1 = (p => p.MemberID == id && p.ExamID == ExamID);
				DAL.DALHelper.Instance.DeleteWithTransaction<TB_Score>(p => p.MemberID == id && p.ExamID == ExamID);

				// 添加数据
				string Msg = "如下数据存在问题，不能为其申请报名:\\r\\n\\r\\n";
				bool flagr = true;

				if (!DI.Tables[0].Columns.Contains("姓名") || !DI.Tables[0].Columns.Contains("性别") || !DI.Tables[0].Columns.Contains("手机号码") || !DI.Tables[0].Columns.Contains("身份证号码") || !DI.Tables[0].Columns.Contains("学员类别(新学员、参加补考及培训、仅参加补考）") || !DI.Tables[0].Columns.Contains("补考科目") || !DI.Tables[0].Columns.Contains("开票情况（合并、单独）若合并开票，请注明合并开票对象") || !DI.Tables[0].Columns.Contains("发票抬头") || !DI.Tables[0].Columns.Contains("培训联系人姓名") || !DI.Tables[0].Columns.Contains("培训联系人手机号") || !DI.Tables[0].Columns.Contains("培训联系人电子邮件地址") || !DI.Tables[0].Columns.Contains("培训发票领取人姓名") || !DI.Tables[0].Columns.Contains("领取人手机号码"))
				{
					ClientScript.RegisterClientScriptBlock(this.GetType(), "AlertMsg", "<script type='text/javascript'>alert('上传的文件格式不正确,请重新上传！！');</script>");
					return;
				}

				int errCount = 0;
				for (int i = 0; i < DI.Tables[0].Rows.Count; i++)
				{
					var NewObjS = DAL.DALHelper.Instance.Create<TB_Score>();

					NewObjS.MemberID = id;
					NewObjS.ExamID = ExamID;
					NewObjS.Name = DI.Tables[0].Rows[i]["姓名"] == null ? string.Empty : DI.Tables[0].Rows[i]["姓名"].ToString();
					NewObjS.Sex = DI.Tables[0].Rows[i]["性别"] == null ? string.Empty : DI.Tables[0].Rows[i]["性别"].ToString();
					NewObjS.IDCardNO = DI.Tables[0].Rows[i]["身份证号码"] == null ? string.Empty : DI.Tables[0].Rows[i]["身份证号码"].ToString();
					NewObjS.CellPhoneNO = DI.Tables[0].Rows[i]["手机号码"] == null ? string.Empty : DI.Tables[0].Rows[i]["手机号码"].ToString();
					NewObjS.StudentType = DI.Tables[0].Rows[i]["学员类别(新学员、参加补考及培训、仅参加补考）"] == null ? string.Empty : DI.Tables[0].Rows[i]["学员类别(新学员、参加补考及培训、仅参加补考）"].ToString();
					NewObjS.ReCourse = DI.Tables[0].Rows[i]["补考科目"] == null ? string.Empty : DI.Tables[0].Rows[i]["补考科目"].ToString();
					NewObjS.InvoicesType = DI.Tables[0].Rows[i]["开票情况（合并、单独）若合并开票，请注明合并开票对象"] == null ? string.Empty : DI.Tables[0].Rows[i]["开票情况（合并、单独）若合并开票，请注明合并开票对象"].ToString();
					NewObjS.InvoicesTitle = DI.Tables[0].Rows[i]["发票抬头"] == null ? string.Empty : DI.Tables[0].Rows[i]["发票抬头"].ToString();
					NewObjS.Train = DI.Tables[0].Rows[i]["培训联系人姓名"] == null ? string.Empty : DI.Tables[0].Rows[i]["培训联系人姓名"].ToString();
					NewObjS.Contact = DI.Tables[0].Rows[i]["培训联系人手机号"] == null ? string.Empty : DI.Tables[0].Rows[i]["培训联系人手机号"].ToString();
					NewObjS.EMail = DI.Tables[0].Rows[i]["培训联系人电子邮件地址"] == null ? string.Empty : DI.Tables[0].Rows[i]["培训联系人电子邮件地址"].ToString();
					NewObjS.InvoicesPerson = DI.Tables[0].Rows[i]["培训发票领取人姓名"] == null ? string.Empty : DI.Tables[0].Rows[i]["培训发票领取人姓名"].ToString();
					NewObjS.InvoicesContact = DI.Tables[0].Rows[i]["领取人手机号码"] == null ? string.Empty : DI.Tables[0].Rows[i]["领取人手机号码"].ToString();
					NewObjS.Score = "-";

					if (string.IsNullOrWhiteSpace(NewObjS.Name) || string.IsNullOrWhiteSpace(NewObjS.IDCardNO) || string.IsNullOrWhiteSpace(NewObjS.CellPhoneNO))
					{
						if (errCount > 0)
							Msg = Msg + ", ";

						flagr = false;
						int fr = i + 2;
						Msg += "第" + fr.ToString() + "行";
						errCount++;
						continue;
					}
					DAL.DALHelper.Instance.Add<TB_Score>(NewObjS);

				}
				
				if (flagr)
				{
					DAL.DALHelper.Instance.SaveChanges();
					ClientScript.RegisterClientScriptBlock(this.GetType(), "AlertMsg", "<script type='text/javascript'>alert('文件上传成功！');location.href='/Education/ApplyList.aspx?st=000500010002';</script>");
					return;
					
				}
				else
				{
					ClientScript.RegisterClientScriptBlock(this.GetType(), "AlertMsg", "<script type='text/javascript'>alert('文件上传失败！" + Msg + "');location.href='/Education/ApplyList.aspx?st=000500010002';</script>");
					return;
				}
			}
			else
			{
				ClientScript.RegisterClientScriptBlock(this.GetType(), "AlertMsg", "<script type='text/javascript'>alert('请退出重新登入后，在上传文件！');location.href='/Education/ApplyList.aspx?st=000500010002';</script>");
				return;
			}
		}

		protected void btn_Download_Click(object sender, EventArgs e)
		{
			ExportToExcel();
		}

		public void ExportToExcel()
		{
			int hid_ExamID;
			if (int.TryParse(this.Exam_ID.Value, out hid_ExamID))
			{
				Expression<Func<TB_Score, bool>> f1 = (p => p.MemberID == this.MID && p.ExamID == hid_ExamID);
				Expression<Func<TB_Score, int>> order = (c => c.ID);
				var resultList = DAL.DALHelper.Instance.GetList<TB_Score, int>(f1, order, true);


				string strTmpFileName = "~/Template/考试报名结果表.xls";
				string strDownloadFileName = "~/Template/Down/考试报名结果表" + DateTime.Now.Ticks + ".xls";
				File.Copy(Server.MapPath(strTmpFileName), Server.MapPath(strDownloadFileName), true);

				string strExcelConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Server.MapPath(strDownloadFileName) + ";Extended Properties='Excel 8.0;HDR=Yes'";

				using (OleDbConnection conn = new OleDbConnection(strExcelConn))
				{
					conn.Open();
					OleDbCommand excmd = new OleDbCommand();
					excmd.Connection = conn;
					foreach (var l in resultList)
					{

						string insertcmd = " INSERT INTO [Sheet1$] (姓名,性别,身份证号码,手机号码,学员类别,补考科目,开票情况,发票抬头,培训联系人姓名,培训联系人手机号,培训联系人电子邮件地址,培训发票领取人姓名,领取人手机号码) VALUES ('" + l.Name + "','" + l.Sex + "','" + l.IDCardNO + "','" + l.CellPhoneNO + "','" + l.StudentType + "','" + l.ReCourse + "','" + l.InvoicesType + "','" + l.InvoicesTitle + "','" + l.Train + "','" + l.Contact + "','" + l.EMail + "','" + l.InvoicesPerson + "','" + l.InvoicesContact + "') ";
						//string insertcmd = " INSERT INTO [Sheet1$] (姓名) VALUES ('" + l.Name + "') ";
						excmd.CommandText = insertcmd;
						excmd.ExecuteNonQuery();
					}
					conn.Close();
				}
				Response.Redirect(strDownloadFileName);
			}
			else
			{
				Response.Redirect("~/Template/考试报名结果表.xls");
			}
		}
	}
}